from datetime import datetime
from typing import Optional

from sqlmodel import exists, func, select, text
from sqlmodel.ext.asyncio.session import AsyncSession

from app.core import settings
from app.models.system import (
    SysDept,
    SysMenu,
    SysRole,
    SysRoleMenu,
    SysUser,
    SysUserRole,
)
from app.schemas.base import Pagination
from app.schemas.user import UserCreate, UserPageQuery, UserUpdate
from app.utils.pwd_utils import get_password_hash, verify_password


async def create_user(*, session: AsyncSession, user_create: UserCreate) -> SysUser:
    """
    异步创建用户

    Args:
        session: 异步数据库会话
        user_create: 用户创建数据传输对象

    Returns:
        SysUser: 创建的用户对象
    """
    # 创建用户对象，对密码进行哈希处理
    db_obj = SysUser.model_validate(user_create, update={"password": get_password_hash(user_create.password)})
    # 添加用户对象到数据库会话
    session.add(db_obj)
    # 提交事务
    await session.commit()
    # 刷新对象以获取数据库生成的ID等信息
    await session.refresh(db_obj)
    return db_obj


async def update_user(*, session: AsyncSession, db_user: SysUser, user_in: UserUpdate) -> SysUser:
    """
    异步更新用户信息

    Args:
        session: 异步数据库会话
        db_user: 数据库中已存在的用户对象
        user_in: 用户更新数据传输对象

    Returns:
        SysUser: 更新后的用户对象
    """
    # 获取需要更新的字段数据
    user_data = user_in.model_dump(exclude_unset=True)
    extra_data = {}

    # 如果密码字段被更新，需要对其进行哈希处理
    if "password" in user_data:
        password = user_data["password"]
        hashed_password = get_password_hash(password)
        extra_data["password"] = hashed_password

    # 更新用户对象
    db_user.sqlmodel_update(user_data, update=extra_data)
    # 添加更新后的用户对象到数据库会话
    session.add(db_user)
    # 提交事务
    await session.commit()
    # 刷新对象以获取最新数据
    await session.refresh(db_user)
    return db_user


async def delete_user(*, session: AsyncSession, user_id: int, update_by: int = None) -> bool:
    """
    异步删除用户（支持软删除和物理删除）

    Args:
        session: 异步数据库会话
        user_id: 用户ID
        update_by: 操作人ID（用于软删除时记录操作人）

    Returns:
        bool: 删除是否成功
    """
    # 通过用户ID获取用户对象
    statement = select(SysUser).where(SysUser.id == user_id)
    result = await session.exec(statement)
    _user = result.one_or_none()

    # 如果用户不存在，返回False表示删除失败
    if not _user:
        return False

    # 检查是否存在软删除字段
    if hasattr(_user, "is_deleted"):
        # 执行软删除：更新删除标识和相关字段
        extra_data = {"update_time": datetime.now(), "is_deleted": 1}
        # 如果提供了操作人ID，则记录
        if update_by:
            extra_data["update_by"] = update_by

        # 更新用户对象
        _user.sqlmodel_update(extra_data)
        # 添加更新后的用户对象到数据库会话
        session.add(_user)
        # 提交事务
        await session.commit()
        return True
    else:
        # 执行物理删除
        await session.delete(_user)
        # 提交事务
        await session.commit()
        return True


async def get_user_by_id(*, session: AsyncSession, user_id: int) -> SysUser | None:
    """
    异步通过用户ID获取用户

    Args:
        session: 异步数据库会话
        user_id: 用户ID

    Returns:
        SysUser | None: 用户对象或None（如果未找到）
    """
    statement = select(SysUser).where(SysUser.id == user_id)
    result = await session.exec(statement)
    return result.one_or_none()


async def get_user_by_username(*, session: AsyncSession, username: str) -> SysUser | None:
    """
    异步通过用户名获取用户

    Args:
        session: 异步数据库会话
        username: 用户名

    Returns:
        SysUser | None: 用户对象或None（如果未找到）
    """
    statement = select(SysUser).where(SysUser.username == username)
    result = await session.exec(statement)
    return result.one_or_none()


async def get_user_by_email(*, session: AsyncSession, email: str) -> SysUser | None:
    """
    异步通过邮箱获取用户

    Args:
        session: 异步数据库会话
        email: 用户邮箱

    Returns:
        SysUser | None: 用户对象或None（如果未找到）
    """
    statement = select(SysUser).where(SysUser.email == email)
    result = await session.exec(statement)
    return result.one_or_none()


async def authenticate(*, session: AsyncSession, username: str, password: str) -> SysUser | None:
    """
    异步验证用户身份（通过用户名）

    Args:
        session: 异步数据库会话
        username: 用户名
        password: 用户密码

    Returns:
        SysUser | None: 验证成功的用户对象或None（如果验证失败）
    """
    # 通过用户名获取用户
    db_user = await get_user_by_username(session=session, username=username)
    # 如果用户不存在或密码验证失败，返回None
    if not db_user or not verify_password(password, db_user.password):
        return None
    return db_user


async def authenticate_by_email(*, session: AsyncSession, email: str, password: str) -> SysUser | None:
    """
    异步验证用户身份（通过邮箱）

    Args:
        session: 异步数据库会话
        email: 用户邮箱
        password: 用户密码

    Returns:
        SysUser | None: 验证成功的用户对象或None（如果验证失败）
    """
    # 通过邮箱获取用户
    db_user = await get_user_by_email(session=session, email=email)
    # 如果用户不存在或密码验证失败，返回None
    if not db_user or not verify_password(password, db_user.password):
        return None
    return db_user


async def get_role_by_userid(session: AsyncSession, user_id: int) -> Optional[list[SysRole]]:
    """
    根据用户ID查询角色信息
    """
    stmt = select(SysRole).join(SysUserRole, SysRole.id == SysUserRole.role_id).where(SysUserRole.user_id == user_id)
    rs = await session.exec(stmt)
    return rs.all()


async def get_menu_by_userid(session: AsyncSession, user_id: int) -> Optional[list[SysMenu]]:
    """
    根据用户ID查询菜单信息

    Args:
        session: 异步数据库会话
        user_id: 用户ID

    Returns:
        Optional[list[SysMenu]]: 菜单列表或None（如果未找到）
    """
    stmt = (
        select(SysMenu)
        .join(SysRoleMenu, SysMenu.id == SysRoleMenu.menu_id)
        .join(SysUserRole, SysRoleMenu.role_id == SysUserRole.role_id)
        .where(SysUserRole.user_id == user_id)
        .distinct()  # 去除重复菜单项
        .order_by(SysMenu.sort)  # 按排序顺序排序
    )
    rs = await session.exec(stmt)
    return rs.all()


async def get_user_page(session: AsyncSession, user_query: UserPageQuery) -> Pagination[SysUser] | None:
    """
    分页查询用户信息
    Args:
        session: 异步数据库会话
        user_query: 用户分页查询参数
    Returns:
        Pagination[SysUser] | None: 用户分页结果或None（如果未找到）
    """
    # 构建查询语句
    stmt = (
        select(
            SysUser.id,
            SysUser.username,
            SysUser.nickname,
            SysUser.mobile,
            SysUser.gender,
            SysUser.avatar,
            SysUser.status,
            SysUser.email,
            SysDept.name.label("dept_name"),
            func.string_agg(SysRole.name, ",").label("roleNames"),
            SysUser.create_time,
        )
        .where(SysUser.is_deleted == 0)
        .join(SysDept, SysDept.id == SysUser.dept_id, isouter=True)
        .join(SysUserRole, SysUserRole.user_id == SysUser.id, isouter=True)
        .join(SysRole, SysRole.id == SysUserRole.role_id, isouter=True)
        .group_by(SysUser.id, SysDept.name)
    )

    if user_query.is_root:
        # 使用 EXISTS 子查询
        subquery = (
            select(SysUserRole.user_id)
            .join(SysRole, SysUserRole.role_id == SysRole.id)
            .where((SysUserRole.user_id == SysUser.id) & (SysRole.code == settings.ADMIN_ROLE_CODE))
        )
        stmt = stmt.where(~exists(subquery))

    # 关键词搜索
    if user_query.keywords and user_query.keywords.strip():
        kw = f"%{user_query.keywords.strip()}%"
        stmt = stmt.where(
            (SysUser.username.ilike(kw))  # PostgreSQL 推荐使用 ILIKE 进行不区分大小写匹配
            | (SysUser.nickname.ilike(kw))
            | (SysUser.mobile.ilike(kw))
        )

    # 状态过滤
    if user_query.status is not None:
        stmt = stmt.where(SysUser.status == user_query.status)

    # 部门过滤 (PostgreSQL 数组操作)
    if user_query.deptId is not None:
        dept_id = str(user_query.deptId)
        # 使用 PostgreSQL 的数组操作符
        stmt = stmt.where(
            text(
                f"array[{user_query.deptId}] <@ string_to_array(dept.tree_path, ',')::int[] OR dept.id = {user_query.deptId}"
            )
        )

    # 创建时间范围 (PostgreSQL 时间戳处理)
    if user_query.createTime and len(user_query.createTime) >= 2:
        start, end = user_query.createTime[0], user_query.createTime[1]

        if start and start.strip():
            # 自动补全时间部分
            start_date = start.strip() + " 00:00:00" if len(start) == 10 else start
            stmt = stmt.where(SysUser.create_time >= text(f"'{start_date}'::timestamp"))

        if end and end.strip():
            end_date = end.strip() + " 23:59:59" if len(end) == 10 else end
            stmt = stmt.where(SysUser.create_time <= text(f"'{end_date}'::timestamp"))

    # 角色过滤
    if user_query.roleIds:
        stmt = stmt.where(SysUserRole.role_id.in_(user_query.roleIds))

    # 动态排序 (使用白名单防止 SQL 注入)
    allowed_fields = ["id", "username", "nickname", "mobile", "create_time", "update_time"]
    if user_query.field and user_query.direction:
        if user_query.field in allowed_fields and user_query.direction.lower() in ["asc", "desc"]:
            order_field = getattr(SysUser, user_query.field)
            if user_query.direction.lower() == "asc":
                stmt = stmt.order_by(order_field.asc())
            else:
                stmt = stmt.order_by(order_field.desc())
        else:
            # 无效排序参数使用默认排序
            stmt = stmt.order_by(SysUser.update_time.desc(), SysUser.create_time.desc())
    else:
        # 默认排序
        stmt = stmt.order_by(SysUser.update_time.desc(), SysUser.create_time.desc())
